package com.alpha.dao;

import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.NamingException;

import com.alpha.bom.Exam;
import com.alpha.bom.PracticeExam;
import com.alpha.bom.RealExam;
import com.alpha.dao.interfaces.IExamDao;

public class ExamDao extends AlphaDao implements IExamDao{
	
	@Override
	public Exam retrieveExamById(int examId) throws SQLException, NamingException {
		Exam exam = null;
		this.connect();
		try {
			String select = "SELECT * FROM tbl_exam LEFT JOIN tbl_category ON tbl_exam.exam_category=tbl_category.category_id WHERE exam_id="+examId;
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				int id = this.resultSet.getInt("exam_id");
				String name = this.resultSet.getString("exam_name");
				boolean real = this.resultSet.getInt("exam_is_real") == 1 ? true : false;
				int profId = this.resultSet.getInt("exam_professor_id");
				String category = this.resultSet.getString("category_name");
				if(real) {
					boolean locked = this.resultSet.getInt("exam_locked") == 1 ? true : false;
					int nStudents = new ExamResponseDao().getNumberOfTakers(examId);
					exam = new RealExam(id, name, profId);
					((RealExam)exam).setNumberOfStudents(nStudents);
					((RealExam)exam).setLocked(locked);
					QuestionDao qDao = new QuestionDao();
					exam.setQuestions(qDao.retrieveQuestions(examId));
					exam.setCategory(category);
				} else {
					exam = new PracticeExam(examId, name);
					exam.setExamType("Practice");
					exam.setProfessorId(profId);
					QuestionDao qDao = new QuestionDao();
					exam.setQuestions(qDao.retrieveQuestions(examId));
					exam.setCategory(category);
				}
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return exam;
	}

	@Override
	public ArrayList<Exam> retrieveExamByProfessorId(int professorId) throws SQLException, NamingException {
		ArrayList<Exam> exams = new ArrayList<Exam>();
		this.connect();
		try {
			String select = "SELECT * FROM tbl_exam LEFT JOIN tbl_category ON tbl_exam.exam_category=tbl_category.category_id WHERE exam_professor_id="+professorId;
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				int id = this.resultSet.getInt("exam_id");
				String name = this.resultSet.getString("exam_name");
				boolean real = this.resultSet.getInt("exam_is_real") == 1 ? true : false;
				int profId = this.resultSet.getInt("exam_professor_id");
				String category = this.resultSet.getString("category_name");
				if(real) {
					boolean locked = this.resultSet.getInt("exam_locked") == 1 ? true : false;
					int nStudents = new ExamResponseDao().getNumberOfTakers(id);
					Exam exam = new RealExam(id, name, profId);
					((RealExam)exam).setNumberOfStudents(nStudents);
					((RealExam)exam).setLocked(locked);
					QuestionDao qDao = new QuestionDao();
					exam.setQuestions(qDao.retrieveQuestions(id));
					exam.setCategory(category);
					exams.add(exam);
				} else {
					Exam exam = new PracticeExam(id, name);
					exam.setExamType("Practice");
					exam.setProfessorId(profId);
					QuestionDao qDao = new QuestionDao();
					exam.setQuestions(qDao.retrieveQuestions(id));
					exam.setCategory(category);
					exams.add(exam);
				}
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return exams;
	}

	@Override
	public ArrayList<Exam> retrieveCompleteExamsByByProfessorId(int professorId) throws SQLException, NamingException {
		ArrayList<Exam> exams = new ArrayList<Exam>();
		this.connect();
		try {
			String select = "SELECT * FROM tbl_exam LEFT JOIN tbl_category ON tbl_exam.exam_category=tbl_category.category_id WHERE exam_professor_id="+professorId+" AND exam_is_real=1";
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				int id = this.resultSet.getInt("exam_id");
				String name = this.resultSet.getString("exam_name");
				boolean real = this.resultSet.getInt("exam_is_real") == 1 ? true : false;
				int profId = this.resultSet.getInt("exam_professor_id");
				String category = this.resultSet.getString("category_name");
				if(real) {
					boolean locked = this.resultSet.getInt("exam_locked") == 1 ? true : false;
					int nStudents = new ExamResponseDao().getNumberOfAllTakers(id);
					if(nStudents > 0) {
						Exam exam = new RealExam(id, name, profId);
						((RealExam)exam).setNumberOfStudents(nStudents);
						((RealExam)exam).setLocked(locked);
						QuestionDao qDao = new QuestionDao();
						exam.setQuestions(qDao.retrieveQuestions(id));
						exam.setCategory(category);
						exams.add(exam);
					}
				} else {
					Exam exam = new PracticeExam(id, name);
					exam.setExamType("Practice");
					exam.setProfessorId(profId);
					QuestionDao qDao = new QuestionDao();
					exam.setQuestions(qDao.retrieveQuestions(id));
					exam.setCategory(category);
					exams.add(exam);
				}
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return exams;
	}

	@Override
	public ArrayList<Exam> retrieveExamByProfessorId(int professorId, boolean real) throws SQLException, NamingException {
		ArrayList<Exam> exams = new ArrayList<Exam>();
		this.connect();
		try {
			String select = "SELECT * FROM tbl_exam LEFT JOIN tbl_category ON tbl_exam.exam_category=tbl_category.category_id WHERE exam_professor_id="+professorId+" AND exam_is_real="+(real ? "1" : "0");
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				int id = this.resultSet.getInt("exam_id");
				String name = this.resultSet.getString("exam_name");
				real = this.resultSet.getInt("exam_is_real") == 1 ? true : false;
				int profId = this.resultSet.getInt("exam_professor_id");
				String category = this.resultSet.getString("category_name");
				if(real) {
					boolean locked = this.resultSet.getInt("exam_locked") == 1 ? true : false;
					int nStudents = new ExamResponseDao().getNumberOfTakers(id);
					Exam exam = new RealExam(id, name, profId);
					((RealExam)exam).setNumberOfStudents(nStudents);
					((RealExam)exam).setLocked(locked);
					QuestionDao qDao = new QuestionDao();
					exam.setQuestions(qDao.retrieveQuestions(id));
					exam.setCategory(category);
					exams.add(exam);
				} else {
					Exam exam = new PracticeExam(id, name);
					exam.setExamType("Practice");
					exam.setProfessorId(profId);
					QuestionDao qDao = new QuestionDao();
					exam.setQuestions(qDao.retrieveQuestions(id));
					exam.setCategory(category);
					exams.add(exam);
				}
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return exams;
	}

	@Override
	public ArrayList<Exam> retrieveAllPracticeExams() throws SQLException, NamingException {
		ArrayList<Exam> exams = new ArrayList<Exam>();
		this.connect();
		try {
			String select = "SELECT * FROM tbl_exam LEFT JOIN tbl_category ON tbl_exam.exam_category=tbl_category.category_id WHERE exam_is_real=0";
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				int id = this.resultSet.getInt("exam_id");
				String name = this.resultSet.getString("exam_name");
				int profId = this.resultSet.getInt("exam_professor_id");
				
				Exam exam = new PracticeExam(id, name);
				exam.setExamType("Practice");
				exam.setProfessorId(profId);
				QuestionDao qDao = new QuestionDao();
				exam.setQuestions(qDao.retrieveQuestions(id));
				String category = this.resultSet.getString("category_name");
				exam.setCategory(category);
				exams.add(exam);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return exams;
	}

	@Override
	public ArrayList<Exam> retrieveExamByCategory(int professorId, String category, boolean real) throws SQLException, NamingException {
		ArrayList<Exam> exams = new ArrayList<Exam>();
		CategoryDao cDao = new CategoryDao();
		int categoryId = cDao.retrieveCategoryId(category);
		this.connect();
		try {
			String select = "SELECT * FROM tbl_exam WHERE exam_professor_id="+professorId+" AND exam_category="+categoryId+" AND exam_is_real="+(real ? 1 : 0);
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				int id = this.resultSet.getInt("exam_id");
				String name = this.resultSet.getString("exam_name");
				real = this.resultSet.getInt("exam_is_real") == 1 ? true : false;
				int profId = this.resultSet.getInt("exam_professor_id");
				if(real) {
					boolean locked = this.resultSet.getInt("exam_locked") == 1 ? true : false;
					int nStudents = new ExamResponseDao().getNumberOfTakers(id);
					Exam exam = new RealExam(id, name, profId);
					((RealExam)exam).setNumberOfStudents(nStudents);
					((RealExam)exam).setLocked(locked);
					QuestionDao qDao = new QuestionDao();
					exam.setQuestions(qDao.retrieveQuestions(id));
					exam.setCategory(category);
					exams.add(exam);
				} else {
					Exam exam = new PracticeExam(id, name);
					exam.setExamType("Practice");
					exam.setProfessorId(profId);
					QuestionDao qDao = new QuestionDao();
					exam.setQuestions(qDao.retrieveQuestions(id));
					exam.setCategory(category);
					exams.add(exam);
				}
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return exams;
	}

	@Override
	public ArrayList<Exam> retrieveExamByCategory(int professorId, String category) throws SQLException, NamingException {
		ArrayList<Exam> exams = new ArrayList<Exam>();
		CategoryDao cDao = new CategoryDao();
		int categoryId = cDao.retrieveCategoryId(category);
		this.connect();
		try {
			String select = "SELECT * FROM tbl_exam WHERE exam_professor_id="+professorId+" AND exam_category="+categoryId;
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				int id = this.resultSet.getInt("exam_id");
				String name = this.resultSet.getString("exam_name");
				boolean real = this.resultSet.getInt("exam_is_real") == 1 ? true : false;
				int profId = this.resultSet.getInt("exam_professor_id");
				if(real) {
					boolean locked = this.resultSet.getInt("exam_locked") == 1 ? true : false;
					int nStudents = new ExamResponseDao().getNumberOfTakers(id);
					Exam exam = new RealExam(id, name, profId);
					((RealExam)exam).setNumberOfStudents(nStudents);
					((RealExam)exam).setLocked(locked);
					QuestionDao qDao = new QuestionDao();
					exam.setQuestions(qDao.retrieveQuestions(id));
					exam.setCategory(category);
					exams.add(exam);
				} else {
					Exam exam = new PracticeExam(id, name);
					exam.setExamType("Practice");
					exam.setProfessorId(profId);
					QuestionDao qDao = new QuestionDao();
					exam.setQuestions(qDao.retrieveQuestions(id));
					exam.setCategory(category);
					exams.add(exam);
				}
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return exams;
	}

	@Override
	public ArrayList<Exam> retrieveExamByStudentId(int studentId) throws NamingException, SQLException {
		ArrayList<Exam> exams = new ArrayList<Exam>();
		this.connect();
		try {
			String select = "SELECT * FROM tbl_exam_student WHERE student_id="+studentId;
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				int examId = this.resultSet.getInt("exam_id");
				ExamDao eDao = new ExamDao();
				Exam exam = eDao.retrieveExamById(examId);
				exams.add(exam);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return exams;
	}

	@Override
	public ArrayList<Exam> retrieveUntakenExamByStudentId(int studentId) throws NamingException, SQLException {
		ArrayList<Exam> exams = new ArrayList<Exam>();
		this.connect();
		try {
			String select = "SELECT * FROM tbl_exam_student WHERE student_id="+studentId+" AND date_taken is NULL";
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				int examId = this.resultSet.getInt("exam_id");
				ExamDao eDao = new ExamDao();
				Exam exam = eDao.retrieveExamById(examId);
				exams.add(exam);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return exams;
	}

	@Override
	public int retrieveLatestExamStudentId(int examId, int studentId) throws NamingException, SQLException {
		int id = -1;
		this.connect();
		try {
			String select = "SELECT exam_student_id FROM tbl_exam_student WHERE exam_id="+examId+" AND student_id="+studentId+" ORDER BY exam_student_id DESC";
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			if(this.resultSet.next()) {
				id = this.resultSet.getInt("exam_student_id");
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return id;
	}

	@Override
	public boolean insertExam(Exam exam) throws NamingException, SQLException {
		boolean success = false;
		this.connect();
		String insert = "INSERT INTO tbl_exam(exam_name, exam_is_real, exam_professor_id, exam_locked, exam_category) VALUES(?,?,?,?,?)";
		try {
			this.preparedStatement = this.connection.prepareStatement(insert);
			this.preparedStatement.setString(1, exam.getExamName());
			this.preparedStatement.setInt(2, exam.getExamType().equals("Real") ? 1 : 0);
			this.preparedStatement.setInt(3, exam.getProfessorId());
			if(exam.getExamType().equals("Real")) {
				this.preparedStatement.setInt(4, ((RealExam)exam).isLocked() ? 1 : 0);
			} else {
				this.preparedStatement.setString(4, null);
			}
			
			CategoryDao cDao = new CategoryDao();
			int categoryId = cDao.retrieveCategoryId(exam.getCategory());
			this.preparedStatement.setInt(5, categoryId);
			
			int rows = this.preparedStatement.executeUpdate(); 
			if(rows > 0) {
				success = true;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return success;
	}

	@Override
	public boolean insertExamStudent(int studentId, int examId) throws NamingException, SQLException {
		boolean success = false;
		this.connect();
		String insert = "INSERT INTO tbl_exam_student(exam_id, student_id) VALUES(?,?)";
		try {
			this.preparedStatement = this.connection.prepareStatement(insert);
			this.preparedStatement.setInt(1, examId);
			this.preparedStatement.setInt(2, studentId);
			
			int rows = this.preparedStatement.executeUpdate(); 
			if(rows > 0) {
				success = true;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return success;
	}

	@Override
	public boolean updateExam(RealExam exam) throws NamingException, SQLException {
		boolean success = false;
		this.connect();
		String update = "UPDATE tbl_exam SET exam_locked=? WHERE exam_id=?";
		try {
			this.preparedStatement = this.connection.prepareStatement(update);
			this.preparedStatement.setInt(1, exam.isLocked() ? 1 : 0);
			this.preparedStatement.setInt(2, exam.getExamId());
			int rows = this.preparedStatement.executeUpdate(); 
			if(rows > 0) {
				success = true;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return success;
	}
	
}
